/*== TSL_prc_DailyStatusRunList ==========================================================================
===============================================================================*/
	--START CODE--
	CREATE PROCEDURE [dbo].[TSL_prc_DailyStatusRunList]
		@StartDate  nvarchar(255) = ''
		,@EndDate	nvarchar(255) = ''
		,@MaxRowsToReturn int = 50
	AS

	SELECT LoadTestRunId AS 'Run #', LoadTestName, StartTime, RunDuration, [Description] FROM LoadTestRun 
		  WHERE 
		  -- Add a second to cover the rounding of milliseconds stored in the DB
		  --AND 
		  (@StartDate = '' OR  @EndDate = '' OR
		   StartTime BETWEEN @StartDate AND DATEADD(second, 1, @EndDate))
		  AND Outcome != 'InProgress' 		    
		  ORDER BY LoadTestRunId DESC
	/*
		;WITH Aggs
		AS	
		(SELECT	
				a.LoadTestRunId
				,COUNT(a.TestDetailId) AS [Total Tests]
				,COUNT(b.TestDetailId) AS [Failed Tests]
				,CAST(AVG(a.ElapsedTime) AS Decimal(15,2)) AS [Response Time]
			FROM 
				dbo.LoadTestTestDetail AS a

			/* LEFT OUTER JOIN means that the left side (which is the TABLE above) will have all
			results returned regardless of the criteria for the JOIN. So in this case, the Total
			Tests, shows everything, but the Failed Tests (which is the same physical table, but 
			is joined with a filter (b.Outcome = 1). 
			*/			
			LEFT OUTER JOIN dbo.LoadTestTestDetail AS b
			ON a.LoadTestRunId = b.LoadTestRunId
			AND a.TestDetailId = b.TestDetailId
			AND b.Outcome = 1
		
			WHERE 
				a.InMeasurementInterval = 1 
			GROUP BY a.LoadTestRunId
			)

		SELECT TOP (@MaxRowsToReturn) a.LoadTestRunId
			  ,a.LoadTestName
			  ,a.Description
			  ,a.StartTime
			  ,a.RunDuration
			  ,b.[Total Tests]
			  ,b.[Failed Tests]
			  ,CAST(b.[Response Time] AS Decimal(15,2)) AS 'Test Response Time'
			  ,CAST((CAST(b.[Failed Tests] AS Decimal(15,2)) / b.[Total Tests]) * 100 AS Decimal(3,0)) AS 'Percent Failed'
			  --,CAST(c.CumulativeValue AS Decimal(15,2)) AS 'Page Response Time'
			  --,CAST(d.CumulativeValue AS Decimal(15,2)) AS 'Pages Per Second'
		  FROM LoadTestRun AS a
	  
		  INNER JOIN Aggs AS b
		  ON a.LoadTestRunId = b.LoadTestRunId
		  
		  --INNER JOIN LoadTestComputedCounterSummary AS c
		  --on a.LoadTestRunId = c.LoadTestRunId
		  --INNER JOIN LoadTestComputedCounterSummary AS d
		  --on a.LoadTestRunId = d.LoadTestRunId
	  
		  WHERE 
		  -- Add a second to cover the rounding of milliseconds stored in the DB
		  --AND 
		  (@StartDate = '' OR  @EndDate = '' OR
		   a.StartTime BETWEEN @StartDate AND DATEADD(second, 1, @EndDate))
		  AND 
		    (@TestNameContains IS NULL or a.LoadTestName LIKE @TestNameContains)
		  AND 
		    (@DescriptionContains IS NULL or a.Description LIKE @DescriptionContains)
		    
		  ORDER BY a.LoadTestRunId DESC

	*/	
	GO
	--START CODE--
	GRANT EXECUTE ON TSL_prc_DailyStatusRunList TO PUBLIC
	GO


/*== TSL_prc_SummaryTimingResultsForReport ===========================================================
* EXAMPLE
EXEC TSL_prc_SummaryTimingResultsForReport 
	@RunId=79, 
	@IncludeTest=1, 
	@IncludeTransaction=1, 
	@IncludePage=1
===============================================================================*/
	--START CODE--
	CREATE PROCEDURE TSL_prc_SummaryTimingResultsForReport
		@RunId  int
		,@IncludeTest bit = 1
		,@IncludeTransaction bit = 1
		,@IncludePage bit = 1
	AS
	BEGIN
		---------------------------------------------------------------------------------

		DECLARE @Results TABLE (
			RunId			INT,
			AgentCount		INT NULL,
			MaxUsers		INT NULL,
			
			TotalTests		INT NULL,
			PassedTests		INT NULL,
			FailedTests		INT NULL,
			AvgTT_All		FLOAT NULL,			--Avg Test Time 
			AvgTT_Pass		FLOAT NULL,			--Avg Test Time 
			TestsSec_All	FLOAT NULL,
			TestsSec_Pass	FLOAT NULL,

			TotalTrans		INT NULL,			--Total Trans
			PassTrans		INT NULL,			--Passed Trans
			AvgTrET_All		FLOAT NULL,			--Avg Trans Elapsed Time
			AvgTrET_Pass	FLOAT NULL,			--Avg Trans Elapsed Time
			AvgTrRT_All		FLOAT NULL,			--Avg Trans Response Time
			AvgTrRT_Pass	FLOAT NULL,			--Avg Trans Response Time
			TransSec_All	FLOAT NULL,			--Transactions Per Second - All
			TransSec_Pass	FLOAT NULL,			--Transactions Per Second - Passed

			TotalPages		INT NULL,			--Total Pages
			PassPages		INT NULL,			--Passed Pages All
			PagesSec_All	FLOAT NULL,			--PagesPerSec - All
			PagesSec_Pass	FLOAT NULL,			--PagesPerSec - Passed
			AvgPgT_All		FLOAT NULL,			--Avg Page Time - Passed
			AvgPgT_Pass		FLOAT NULL			--Avg Page Time - All
		)

		---------------------------------------------------------------------------------
		-- Core Summary Info
		---------------------------------------------------------------------------------
		INSERT INTO @Results (RunId)
			SELECT @RunId

		---------------------------------------------------------------------------------
		-- Agent count and max user count
		---------------------------------------------------------------------------------
		UPDATE @Results	SET	AgentCount = (SELECT COUNT(AgentId) FROM LoadTestRunAgent WHERE LoadTestRunId = @RunId)
		UPDATE @Results	SET	MaxUsers = (SELECT TOP 1 ComputedValue FROM LoadTestComputedCounterSample WHERE LoadTestRunId = @RunId 
			AND CategoryName = 'LoadTest:Scenario' AND CounterName = 'User Load' AND InstanceName = '_Total' ORDER BY ComputedValue DESC)

		---------------------------------------------------------------------------------
		-- Test Count and Avg test time
		---------------------------------------------------------------------------------
		IF @IncludeTest = 1
		BEGIN
			;WITH Aggs
			AS	(SELECT	
					COUNT(a.TestDetailId) AS [Total]
					,CAST(CAST(COUNT(a.TestDetailId) AS Decimal) / MIN(b.RunDuration) AS Decimal(15,2)) AS [TestsSec]
					,CAST(AVG(a.ElapsedTime) AS Decimal(15,2)) AS [Response]
				FROM 
					dbo.LoadTestTestDetail AS a
				INNER JOIN 
					LoadTestRun AS b 
					ON a.LoadTestRunId = b.LoadTestRunId
				WHERE 
					a.LoadTestRunId = @RunId 
					AND a.InMeasurementInterval = 1 
					--AND a.Outcome = 10
				)

			UPDATE @Results
			SET
				TotalTests = [Total]
				,TestsSec_All = [TestsSec]
				,AvgTT_All = [Response] 
			FROM Aggs
	
			;WITH Aggs
			AS	(SELECT	
					COUNT(a.TestDetailId) AS [Total]
					,CAST(CAST(COUNT(a.TestDetailId) AS Decimal) / MIN(b.RunDuration) AS Decimal(15,2)) AS [TestsSec]
					,CAST(AVG(a.ElapsedTime) AS Decimal(15,2)) AS [Response]
				FROM 
					dbo.LoadTestTestDetail AS a
				INNER JOIN 
					LoadTestRun AS b 
					ON a.LoadTestRunId = b.LoadTestRunId
				WHERE 
					a.LoadTestRunId = @RunId 
					AND a.InMeasurementInterval = 1 
					AND a.Outcome = 10
				)

			UPDATE @Results
			SET
				PassedTests = [Total]
				,TestsSec_Pass = [TestsSec]
				,AvgTT_Pass = [Response] 
			FROM Aggs
		END

		---------------------------------------------------------------------------------
		-- Transactional Count and Avg trans times
		---------------------------------------------------------------------------------
		IF @IncludeTransaction = 1
		BEGIN
			;WITH Aggs
			AS	(SELECT
				COUNT(DISTINCT (c.TransactionDetailId)) AS [Total]
				,CAST(CAST(COUNT(c.InMeasurementInterval) AS Decimal) / MIN(b.RunDuration) AS Decimal(15,2)) AS [TransSec]
				,CAST(AVG(c.ElapsedTime) AS Decimal(15,2)) AS [Elapsed]
				,CAST(AVG(c.ResponseTime) AS Decimal(15,2)) AS [Response]
			FROM 
				dbo.LoadTestTestDetail AS a
			INNER JOIN 
				LoadTestRun AS b 
				ON a.LoadTestRunId = b.LoadTestRunId
			INNER JOIN dbo.LoadTestTransactionDetail AS c 
				ON a.LoadTestRunId = c.LoadTestRunId 
				AND a.TestDetailId = c.TestDetailId
			WHERE 
				a.InMeasurementInterval = 1 
				AND a.LoadTestRunId = @RunId
				--AND a.Outcome = 10
				)
		
			UPDATE @Results
			SET
				TotalTrans = [Total]
				,TransSec_All = [TransSec]
				,AvgTrET_All = [Elapsed]
				,AvgTrRT_All = [Response] 
			FROM Aggs

			;WITH Aggs
			AS	(SELECT
				COUNT(DISTINCT (c.TransactionDetailId)) AS [Total]
				,CAST(CAST(COUNT(c.InMeasurementInterval) AS Decimal) / MIN(b.RunDuration) AS Decimal(15,2)) AS [TransSec]
				,CAST(AVG(c.ElapsedTime) AS Decimal(15,2)) AS [Elapsed]
				,CAST(AVG(c.ResponseTime) AS Decimal(15,2)) AS [Response]
			FROM 
				dbo.LoadTestTestDetail AS a
			INNER JOIN 
				LoadTestRun AS b 
				ON a.LoadTestRunId = b.LoadTestRunId
			INNER JOIN dbo.LoadTestTransactionDetail AS c 
				ON a.LoadTestRunId = c.LoadTestRunId 
				AND a.TestDetailId = c.TestDetailId
			WHERE 
				a.InMeasurementInterval = 1 
				AND a.LoadTestRunId = @RunId
				AND a.Outcome = 10
				)
		
			UPDATE @Results
			SET
				PassTrans = [Total]
				,TransSec_Pass = [TransSec]
				,AvgTrET_Pass = [Elapsed]
				,AvgTrRT_Pass = [Response] 
			FROM Aggs
		END

		---------------------------------------------------------------------------------
		-- Page Count and Avg Page times
		---------------------------------------------------------------------------------
		IF @IncludePage = 1
		BEGIN
			;WITH Aggs
			AS	(SELECT
					COUNT(DISTINCT (c.PageDetailId)) AS [Total]
					,CAST(CAST(COUNT(c.InMeasurementInterval) AS Decimal) / MIN(b.RunDuration) AS Decimal(15,2)) AS [PagesSec]
					,CAST(AVG(c.ResponseTime) AS Decimal(15,2)) AS [Response]
				FROM 
					dbo.LoadTestTestDetail AS a
				INNER JOIN 
					LoadTestRun AS b 
					ON a.LoadTestRunId = b.LoadTestRunId
				INNER JOIN dbo.LoadTestPageDetail AS c 
					ON a.LoadTestRunId = c.LoadTestRunId 
					AND a.TestDetailId = c.TestDetailId
				WHERE 
					a.InMeasurementInterval = 1 
					AND a.LoadTestRunId = @RunId
					--AND a.Outcome = 10
				)

			UPDATE @Results
			SET
				TotalPages = [Total]
				,PagesSec_All = [PagesSec]
				,AvgPgT_All = [Response] 
			FROM Aggs
		
			;WITH Aggs
			AS	(SELECT
					COUNT(DISTINCT (c.PageDetailId)) AS [Total]
					,CAST(CAST(COUNT(c.InMeasurementInterval) AS Decimal) / MIN(b.RunDuration) AS Decimal(15,2)) AS [PagesSec]
					,CAST(AVG(c.ResponseTime) AS Decimal(15,2)) AS [Response]
				FROM 
					dbo.LoadTestTestDetail AS a
				INNER JOIN 
					LoadTestRun AS b 
					ON a.LoadTestRunId = b.LoadTestRunId
				INNER JOIN dbo.LoadTestPageDetail AS c 
					ON a.LoadTestRunId = c.LoadTestRunId 
					AND a.TestDetailId = c.TestDetailId
				WHERE 
					a.InMeasurementInterval = 1 
					AND a.LoadTestRunId = @RunId
					AND a.Outcome = 10
				)

			UPDATE @Results
			SET
				PassPages = [Total]
				,PagesSec_Pass = [PagesSec]
				,AvgPgT_Pass = [Response] 
			FROM Aggs
		END
		
		---------------------------------------------------------------------------------
		-- Number of Failed tests
		---------------------------------------------------------------------------------
		UPDATE @Results	
		SET FailedTests = TotalTests - PassedTests

		---------------------------------------------------------------------------------
		-- Return the full temp table
		---------------------------------------------------------------------------------
		SELECT * FROM @Results
	END
	GO
	--START CODE--
	GRANT EXECUTE ON TSL_prc_SummaryTimingResultsForReport TO PUBLIC
	GO


/*== TSL_prc_SummaryTimingResultsUsingPerfmon ===========================================================
* EXAMPLE
EXEC TSL_prc_SummaryTimingResultsUsingPerfmon 
	@RunId=79, 
	@IncludeTest=1, 
	@IncludeTransaction=1, 
	@IncludePage=1
===============================================================================*/
	--START CODE--
	CREATE PROCEDURE TSL_prc_SummaryTimingResultsUsingPerfmon
		@RunId  int
		,@IncludeTest bit = 1
		,@IncludeTransaction bit = 1
		,@IncludePage bit = 1
	AS
	BEGIN
		---------------------------------------------------------------------------------

		DECLARE @Results TABLE (
			RunId			INT,
			AgentCount		INT NULL,
			MaxUsers		INT NULL,
			
			TotalTests		INT NULL,
			PassedTests		INT NULL,
			FailedTests		INT NULL,
			AvgTT_All		FLOAT NULL,			--Avg Test Time 
			AvgTT_Pass		FLOAT NULL,			--Avg Test Time 
			TestsSec_All	FLOAT NULL,
			TestsSec_Pass	FLOAT NULL,

			TotalTrans		INT NULL,			--Total Trans
			PassTrans		INT NULL,			--Passed Trans
			AvgTrET_All		FLOAT NULL,			--Avg Trans Elapsed Time
			AvgTrET_Pass	FLOAT NULL,			--Avg Trans Elapsed Time
			AvgTrRT_All		FLOAT NULL,			--Avg Trans Response Time
			AvgTrRT_Pass	FLOAT NULL,			--Avg Trans Response Time
			TransSec_All	FLOAT NULL,			--Transactions Per Second - All
			TransSec_Pass	FLOAT NULL,			--Transactions Per Second - Passed

			TotalPages		INT NULL,			--Total Pages
			PassPages		INT NULL,			--Passed Pages All
			PagesSec_All	FLOAT NULL,			--PagesPerSec - All
			PagesSec_Pass	FLOAT NULL,			--PagesPerSec - Passed
			AvgPgT_All		FLOAT NULL,			--Avg Page Time - Passed
			AvgPgT_Pass		FLOAT NULL			--Avg Page Time - All
		)

		---------------------------------------------------------------------------------
		-- Core Summary Info
		---------------------------------------------------------------------------------
		INSERT INTO @Results (RunId)
			SELECT @RunId

		---------------------------------------------------------------------------------
		-- Agent count and max user count
		---------------------------------------------------------------------------------
		UPDATE @Results	SET	AgentCount = (SELECT COUNT(AgentId) FROM LoadTestRunAgent WHERE LoadTestRunId = @RunId)
		UPDATE @Results	SET	MaxUsers = (SELECT TOP 1 ComputedValue FROM LoadTestComputedCounterSample WHERE LoadTestRunId = @RunId 
			AND CategoryName = 'LoadTest:Scenario' AND CounterName = 'User Load' AND InstanceName = '_Total' ORDER BY ComputedValue DESC)


		---------------------------------------------------------------------------------
		-- Test Count and Avg test time
		---------------------------------------------------------------------------------
		IF @IncludeTest = 1
		BEGIN

			UPDATE @Results	SET TotalTests = (SELECT CAST(CumulativeValue AS Decimal(15,2)) FROM LoadTestComputedCounterSummary
				WHERE LoadTestRunId = @RunId AND CategoryName = 'LoadTest:Test'
				AND CounterName = 'Total Tests' AND InstanceName = '_Total')

			UPDATE @Results	SET PassedTests = (SELECT CAST(CumulativeValue AS Decimal(15,2)) FROM LoadTestComputedCounterSummary
				WHERE LoadTestRunId = @RunId AND CategoryName = 'LoadTest:Test'
				AND CounterName = 'Passed Tests' AND InstanceName = '_Total')

			UPDATE @Results	SET TestsSec_All = (SELECT CAST(CumulativeValue AS Decimal(15,2)) FROM LoadTestComputedCounterSummary
				WHERE LoadTestRunId = @RunId AND CategoryName = 'LoadTest:Test'
				AND CounterName = 'Tests/Sec' AND InstanceName = '_Total')

			UPDATE @Results	SET AvgTT_All = (SELECT CAST(CumulativeValue AS Decimal(15,2)) FROM LoadTestComputedCounterSummary
				WHERE LoadTestRunId = @RunId AND CategoryName = 'LoadTest:Test'
				AND CounterName = 'Avg. Test Time' AND InstanceName = '_Total')

/*
Total Tests
Passed Tests
Failed Tests
Tests/Sec
Passed Tests/Sec
Failed Tests/Sec
Avg. Requests/Test
Avg. Test Time
% Time in LoadTestPlugin
% Time in WebTest code
% Time in Rules

Total Transactions
Avg. Transaction Time
Avg. Response Time
Transactions/Sec

Total Pages
Avg. Page Time
Page Response Time Goal
% Pages Meeting Goal
Pages/Sec
*/
		END

		---------------------------------------------------------------------------------
		-- Transactional Count and Avg trans times
		---------------------------------------------------------------------------------
		IF @IncludeTransaction = 1
		BEGIN
		
			UPDATE @Results	SET TotalTrans = (SELECT CAST(CumulativeValue AS Decimal(15,2)) FROM LoadTestComputedCounterSummary
				WHERE LoadTestRunId = @RunId AND CategoryName = 'LoadTest:Transaction'
				AND CounterName = 'Total Transactions' AND InstanceName = '_Total')

			UPDATE @Results	SET TransSec_All = (SELECT CAST(CumulativeValue AS Decimal(15,2)) FROM LoadTestComputedCounterSummary
				WHERE LoadTestRunId = @RunId AND CategoryName = 'LoadTest:Transaction'
				AND CounterName = 'Transactions/Sec' AND InstanceName = '_Total')

			UPDATE @Results	SET AvgTrET_All = (SELECT CAST(CumulativeValue AS Decimal(15,2)) FROM LoadTestComputedCounterSummary
				WHERE LoadTestRunId = @RunId AND CategoryName = 'LoadTest:Transaction'
				AND CounterName = 'Avg. Transaction Time' AND InstanceName = '_Total')

			UPDATE @Results	SET AvgTrRT_All = (SELECT CAST(CumulativeValue AS Decimal(15,2)) FROM LoadTestComputedCounterSummary
				WHERE LoadTestRunId = @RunId AND CategoryName = 'LoadTest:Transaction'
				AND CounterName = 'Avg. Response Time' AND InstanceName = '_Total')

		END

		---------------------------------------------------------------------------------
		-- Page Count and Avg Page times
		---------------------------------------------------------------------------------
		IF @IncludePage = 1
		BEGIN

			UPDATE @Results	SET TotalPages = (SELECT CAST(CumulativeValue AS Decimal(15,2)) FROM LoadTestComputedCounterSummary
				WHERE LoadTestRunId = @RunId AND CategoryName = 'LoadTest:Page'
				AND CounterName = 'Total Pages' AND InstanceName = '_Total')

			UPDATE @Results	SET PagesSec_All = (SELECT CAST(CumulativeValue AS Decimal(15,2)) FROM LoadTestComputedCounterSummary
				WHERE LoadTestRunId = @RunId AND CategoryName = 'LoadTest:Page'
				AND CounterName = 'Pages/Sec' AND InstanceName = '_Total')

			UPDATE @Results	SET AvgPgT_All = (SELECT CAST(CumulativeValue AS Decimal(15,2)) FROM LoadTestComputedCounterSummary
				WHERE LoadTestRunId = @RunId AND CategoryName = 'LoadTest:Page'
				AND CounterName = 'Avg. Page Time' AND InstanceName = '_Total')

		END
		
		---------------------------------------------------------------------------------
		-- Number of Failed tests
		---------------------------------------------------------------------------------
			UPDATE @Results	SET FailedTests = (SELECT CAST(CumulativeValue AS Decimal(15,2)) FROM LoadTestComputedCounterSummary
				WHERE LoadTestRunId = @RunId AND CategoryName = 'LoadTest:Test'
				AND CounterName = 'Failed Tests' AND InstanceName = '_Total')

		---------------------------------------------------------------------------------
		-- Return the full temp table
		---------------------------------------------------------------------------------
		SELECT * FROM @Results
	END
	GO
	--START CODE--
	GRANT EXECUTE ON TSL_prc_SummaryTimingResultsUsingPerfmon TO PUBLIC
	GO
	